package com.xinzhi.dao.impl;

import com.xinzhi.dao.IBookDao;
import com.xinzhi.entiey.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements IBookDao{


    @Override
    public int addBook(Book book) {
        int count = -1;
        Connection conn = null;
        Statement stat = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
             stat = conn.createStatement();
            String sql = "insert into book values('"+book.getBookid()+"','"+book.getLb()+"','"+book.getCbs()+"" +
                    "','"+book.getZz()+"','"+book.getName()+"','"+book.getPice()+"')";
             count = stat.executeUpdate(sql);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stat!=null) {
                    stat.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }

    @Override
    public int updateBook(Book book) {
        int count = -1;
        Connection conn = null;
        Statement stat = null;
        int num = 0;//计数器
        //动态sql
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            stat = conn.createStatement();
            String sql = "update book set ";
            if(book.getBookid()!=0){
                sql +="bookid='"+book.getBookid()+"'";
                num = 1;
            }
            if(book.getLb()!=null){
                if(num == 1){
                    sql+=",";
                }
                sql +="lb='"+book.getLb()+"'";
                num = 1;
            }
            if(book.getCbs()!=null){
                if(num == 1){
                    sql+=",";
                }
                sql +="cbs='"+book.getCbs()+"'";
                num = 1;
            }
            if(book.getZz()!=null){
                if(num == 1){
                    sql+=",";
                }
                sql +="zz='"+book.getZz()+"'";
                num = 1;
            }
            if(book.getName()!=null){
                if(num == 1){
                    sql+=",";
                }
                sql +="name='"+book.getName()+"'";
                num = 1;
            }
            if(book.getPice()!=null){
                if(num == 1){
                    sql+=",";
                }
                sql +="pice='"+book.getPice()+"'";
                num = 1 ;
            }
            sql+="where bookid="+book.getBookid();
            System.out.println(sql);
            if(num == 1) {
                count = stat.executeUpdate(sql);
            }else {
                count = 0 ;
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stat!=null) {
                    stat.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }

    @Override
    public int deleteBook(int id) {
        int count = -1;
        Connection conn = null;
        Statement stat = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            stat = conn.createStatement();
            String sql = "delete from book where bookid="+id;
            count = stat.executeUpdate(sql);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stat!=null) {
                    stat.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }

    @Override
    public List<Book> getAll() {
        int count = -1;
        Connection conn = null;
        Statement stat = null;
        List<Book> books = new ArrayList<>();
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            stat = conn.createStatement();
            String sql = "select bookid,lb,cbs,zz,name,pice from book";
            rs = stat.executeQuery(sql);
            while (rs.next()){
                Book book = new Book();
                book.setBookid(rs.getInt("bookid"));
                book.setLb(rs.getString("lb"));
                book.setCbs(rs.getString("cbs"));
                book.setZz(rs.getString("zz"));
                book.setName(rs.getString("name"));
                book.setPice(rs.getString("pice"));
                books.add(book);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null){
                    rs.close();
                }
                if(stat!=null) {
                    stat.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return books;
    }

    @Override
    public Book getBookId(int id) {
        int count = -1;
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        Book book = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            stat = conn.createStatement();
            String sql = "select bookid,lb,cbs,zz,name,pice from book where bookid="+id;
            rs = stat.executeQuery(sql);
            if (rs.next()){
                book = new Book();
                book.setBookid(rs.getInt("bookid"));
                book.setLb(rs.getString("lb"));
                book.setCbs(rs.getString("cbs"));
                book.setZz(rs.getString("zz"));
                book.setName(rs.getString("name"));
                book.setPice(rs.getString("pice"));
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stat!=null) {
                    stat.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return book;
    }

}
